package com.example.book_subscribe.adminBookList.mapper;

import com.example.book_subscribe.adminBookList.dto.AdminBookDetailDto;
import com.example.book_subscribe.adminBookList.dto.AdminBookListDto;
import com.example.book_subscribe.adminBookList.result.*;
import com.example.book_subscribe.bookList.dto.BookProgressDto;
import com.example.book_subscribe.bookList.result.BookCommentsResult;
import com.example.book_subscribe.mine.result.MineResult;
import org.apache.ibatis.annotations.*;

import java.util.ArrayList;

@Mapper
public interface AdminBookListMapper {
//    @Select("SELECT b.book_name, b.book_author, b.book_url, c.category_name " +
//            "FROM book_list b " +
//            "JOIN category c ON b.book_type = c.category_type " +
//            "LIMIT #{offset}, #{adminBookListDto.limit};")
//    ArrayList<AdminBookListResult> searchAdminBookList(@Param("adminBookListDto") AdminBookListDto adminBookListDto,
//                                                       @Param("offset") long offset);
@Select("<script>" +
        "SELECT b.book_id,b.book_name, b.book_author, b.book_url, c.category_name, " +
        "       (SELECT COUNT(*) FROM comment_list cl WHERE cl.book_name = b.book_name) AS comment_number, " +
        "       (SELECT COUNT(*) FROM subscribe s WHERE s.book_name = b.book_name AND s.state = 1) AS subscribe_number " +
        "FROM book_list b " +
        "JOIN category c ON b.book_type = c.category_type " +
        "<where>" +
        "   <if test='adminBookListDto.bookName != null and adminBookListDto.bookName != \"\"'>" +
        "       AND b.book_name LIKE CONCAT('%', #{adminBookListDto.bookName}, '%')" +
        "   </if>" +
        "   <if test='adminBookListDto.author != null and adminBookListDto.author != \"\"'>" +
        "       AND b.book_author LIKE CONCAT('%', #{adminBookListDto.author}, '%')" +
        "   </if>" +
        "   <if test='adminBookListDto.type != null and adminBookListDto.type != \"\"'>" +
        "       AND b.book_type = #{adminBookListDto.type}" +
        "   </if>" +
        "</where>" +
        "LIMIT #{offset}, #{adminBookListDto.limit}" +
        "</script>")
ArrayList<AdminBookListResult> searchAdminBookList(
        @Param("adminBookListDto") AdminBookListDto adminBookListDto,
        @Param("offset") long offset
);
    @Select("<script>" +
            "SELECT COUNT(*) FROM book_list b " +
            "<where>" +
            "   <if test='bookName != null and bookName != \"\"'>" +
            "       AND b.book_name LIKE CONCAT('%', #{bookName}, '%')" +
            "   </if>" +
            "   <if test='author != null and author != \"\"'>" +
            "       AND b.book_author LIKE CONCAT('%', #{author}, '%')" +
            "   </if>" +
            "   <if test='type != null and type != \"\"'>" +
            "       AND b.book_type = #{type}" +
            "   </if>" +
            "</where>" +
            "</script>")
    int countBooks(AdminBookListDto dto);

    @Select("SELECT b.book_id,b.book_name, b.book_author, b.book_url, b.book_introduce, b.book_file, " +
            "c.category_type, " +
            "cp.category_parent_type " +
            "FROM book_list b " +
            "JOIN category c ON b.book_type = c.category_type " +
            "JOIN category_parent cp ON c.category_parent = cp.category_parent_name " +
            "WHERE b.book_id = #{bookId}")
    AdminBookDetailResult getAdminBookDetail(@Param("bookId") String bookId);


    ArrayList<AllCategoryResult> searchAllCategory();

    @Update("update book_list set book_name=#{bookDetail.bookName},book_author=#{bookDetail.author}, book_type = #{bookDetail.bookType}, book_url = #{bookDetail.coverUrl}, book_introduce = #{bookDetail.description}, book_file = #{bookDetail.fileUrl} where book_id = #{bookDetail.bookId}")
    void updateBookDetails(@Param("bookDetail") AdminBookDetailDto bookDetail);

    @Insert("insert into book_list (book_id,book_name, book_author, book_type, book_url, book_introduce, book_file) " +
            "values (#{bookDetail.bookId},#{bookDetail.bookName}, #{bookDetail.author}, #{bookDetail.bookType}, #{bookDetail.coverUrl}, #{bookDetail.description}, #{bookDetail.fileUrl})")
    void insertBookDetails(@Param("bookDetail") AdminBookDetailDto bookDetail);

    @Delete("delete from book_list where book_id = #{bookId}")
    void deleteBookDetails(@Param("bookId") String bookId);

    @Select("select count(*) from book_list where book_name=#{bookName}")
    boolean searchBookExist(@Param("bookName") String bookName);

    @Select("SELECT u.user_url, u.user_wxname, u.open_id, " +
            "COUNT(DISTINCT c.id) AS comment_num, " +
            "COUNT(DISTINCT CASE WHEN s.state = 1 THEN s.id END) AS subscribe_num " +
            "FROM user u " +
            "LEFT JOIN comment_list c ON u.open_id = c.open_id " +
            "LEFT JOIN subscribe s ON u.open_id = s.open_id " +
            "WHERE u.open_id IS NOT NULL " +
            "GROUP BY u.open_id, u.user_url, u.user_wxname")
    ArrayList<AdminUserAllResult> getAllUser();



    @Select("SELECT c.user_comments, c.comments_content, u.user_url, u.user_wxname, c.`like`" +
            "FROM comment_list c " +
            "JOIN user u ON c.open_id = u.open_id " +
            "WHERE c.book_id = #{bookId} " +
            "ORDER BY c.like DESC")
    ArrayList<AdminAllCommentResult> getAllComment(@Param("bookId") String bookId);
    @Select("SELECT bl.book_name,bl.book_author,bl.book_url, c.user_comments, c.comments_content,  c.`like`" +
            "FROM comment_list c " +
            "JOIN book_list bl ON c.book_id = bl.book_id " +
            "WHERE c.open_id = #{bookId} " +
            "ORDER BY c.like DESC")
    ArrayList<AdminAllUserCommentsResult> getAllUserComment(@Param("bookId") String bookId);
    @Select("SELECT bl.book_name,bl.book_author,bl.book_url " +
            "FROM subscribe s " +
            "JOIN book_list bl ON s.book_id = bl.book_id " +
            "WHERE s.open_id = #{bookId} " +
            "and s.state=1 ")
    ArrayList<AdminAllUserSubscribesResult> getAllUserSubscribe(@Param("bookId") String bookId);
}
